﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBkp_Differential]
#-- Purpose:		Performs a differential backup of a database.  Chooses between
#--					LiteSpeed if available, otherwise standard Backup
#--	Last Update:	04/08/2016
#--					For a complete history - please review comments in Version
#--					Control.
#-- Optional XPs:	[master].[dbo].[xp_backup_database]
#-- Called By:		[dbo].[spBkp_DifferentialAll]
#--					Restore Database Utility Executable
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBkp_Differential]
(
	@database_name		sysname,
	@verify				bit					= 1,
	@label				nvarchar(128)		= NULL,
	@ignore_skip		bit					= 0
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@dir varchar(1500), @sCommand nvarchar(2000), @file_name varchar(1500),
			@readonly bit, @offline bit, @isEndOfMonth bit, @hasFullBackup bit,
			@current_date datetime, @job_name_filter varchar(125),
			@do_full_instead bit, @is_standby bit,
			@hadr_enabled bit, @preferred_replica bit, @sSQL nvarchar(MAX)
DECLARE		@progname sysname
DECLARE @tPreferredReplica TABLE ([yesno] bit)

--- Define the name of this program (for logs)
SELECT		@progname = N'spBkp_Differential'

--- Ensure the database skips backup if configured not to backup
IF ISNULL(@ignore_skip, 0) = 0 
	AND EXISTS (SELECT TOP 1 database_name FROM [dbo].[vwMaint_DoNotBackup] WHERE database_name = @database_name)
  BEGIN
	RETURN
  END

--- @database_name cannot be tempdb
SELECT	@database_name = LTRIM(RTRIM(ISNULL(@database_name, '')))
IF @database_name = N'tempdb'
  BEGIN
	PRINT 'tempdb database cannot be backed up (' + @database_name + ')'
	RETURN
  END

--- Don't do anything if the database is not online
SELECT	@offline = ISNULL( [dbo].[fnConfig_IsDatabaseOffline] ( @database_name ), 0 )
IF @offline = 1
  BEGIN
	SELECT @sCommand = N'The database [' + @database_name + N'] is not online'
	RAISERROR (@sCommand, 0, 0) WITH NOWAIT
	RETURN
  END

--- @database_name is a mandatory fields
IF ISNULL(@database_name, N'') = N''
  BEGIN
	RAISERROR ('The name of the database to backup is required (@database_name)', 16, 1)
	RETURN
  END

--- @database_name must be an existing database
IF NOT EXISTS (SELECT TOP 1 name FROM [master].[sys].[databases] WHERE name = @database_name)
  BEGIN
	RAISERROR ('The requested database does not exist', 16, 1)
	RETURN
  END

--- Snapshot databases cannot be backed up
IF EXISTS (SELECT TOP 1 name FROM [master].[sys].[databases] WHERE name = @database_name AND source_database_id IS NOT NULL)
  BEGIN
	PRINT 'Snapshot databases cannot be backed up (' + @database_name + ')'
	RETURN
  END

ELSE 
  BEGIN
	BEGIN TRY
		SELECT	@hadr_enabled = ISNULL(CAST(SERVERPROPERTY('IsHadrEnabled') as bit), 0),
				@preferred_replica = 1
	
		--- Is this the preferred backup location
		IF OBJECT_ID('[sys].[dm_hadr_availability_group_states]') IS NOT NULL
		  BEGIN
			SELECT	@sSQL = REPLACE('SELECT preferred_replica = [sys].[fn_hadr_backup_is_preferred_replica](''[VAR_DBNAME]'')', '[VAR_DBNAME]', @database_name)
			INSERT INTO @tPreferredReplica EXEC(@sSQL)
			SELECT @preferred_replica = [yesno] FROM @tPreferredReplica
		  END

		IF @hadr_enabled = 0 OR (@hadr_enabled = 1 AND @preferred_replica = 1)
		  BEGIN
			--- Determine if a differential will be large enough that a full backup should be run instead
			EXEC	@do_full_instead = [dbo].[spBkp_ShouldTakeFull] @database_name = @database_name
			IF @do_full_instead = 1
			  BEGIN
				EXEC [dbo].[spBkp_Full] @database_name = @database_name, @verify = @verify, @label = @label
			  END
			ELSE
			  BEGIN
				--- Get Current date/time stamp and set base job name
				SELECT	@current_date = GETDATE(),
						@job_name_filter = 'DBA: Backup Differential'
	
				--- Get database settings
				SELECT	@hasFullBackup = ISNULL( [dbo].[fnBkp_HasFullBackup] ( @database_name ), 0 ),
						@offline = ISNULL( [dbo].[fnConfig_IsDatabaseOffline] ( @database_name ), 0 ),
						@readonly = ISNULL( [dbo].[fnConfig_IsDatabaseReadOnly] ( @database_name ), 0 ),
						@isEndOfMonth = ISNULL( [dbo].[fnConfig_IsEndOfMonth](), 0 ),
						@is_standby = ISNULL( [dbo].[fnConfig_IsDatabaseStandby] ( @database_name ), 0 )

				--- Determine if there is a label for the job execution
				IF @offline = 0 AND @is_standby = 0
				  BEGIN
					IF @database_name = N'master' OR @hasFullBackup = 0 -- OR @isEndOfMonth = 1
					  BEGIN
						EXEC	[dbo].[spBkp_Full] @database_name = @database_name, @verify = @verify, @label = @label
					  END
					ELSE IF @readonly = 0
					  BEGIN
						--- Ensure the directory exists  (xp)
						EXEC	[dbo].[spBkp_GetDirectory] @database_name = @database_name, @backup_directory_name = @dir OUTPUT 
						EXEC	[dbo].[spIO_CreateDirectory] @dir

						--- Get File Name
						EXEC	[dbo].[spBkp_GetDifferentialBackupName] @database_name = @database_name, @backup_directory_name = @file_name OUTPUT

						--- Build the command to backup the database
						IF EXISTS(select * from [master].[dbo].[sysobjects] where id = object_id(N'[master].[dbo].[xp_backup_database]'))
						  BEGIN
							SELECT	@sCommand = 'EXEC [master].[dbo].[xp_backup_database] @database = ''' + @database_name + ''', @filename = ''' + @file_name + ''', @with=''DIFFERENTIAL'''

							IF @label IS NOT NULL
								SELECT	@sCommand = @sCommand + ', @desc=''' + @label + ''''
						  END
						ELSE
						  BEGIN
							SELECT	@sCommand = 'BACKUP DATABASE [' + @database_name + '] TO DISK = ''' + @file_name + ''' WITH DIFFERENTIAL'

							--- Set the backup label
							IF @label IS NOT NULL
							  BEGIN
								SELECT	@sCommand = @sCommand + ', NAME = ''' + @label + ''''
							  END

							IF @verify = 1
							  BEGIN
								SELECT @sCommand = @sCommand + ', CHECKSUM; RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''''
							  END
						  END

						--- Execute the command (backup the database)
						IF LEN(@sCommand) > 0
						  BEGIN
							EXEC	(@sCommand)
							EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sCommand
						  END


						--- Also take a log backup to provide another recovery path to this point (all tlogs or diff)
						EXEC	dbo.spBkp_Log	@database_name = @database_name, @verify = @verify, @label = @label, @ignore_skip = @ignore_skip
					  END
				  END
			  END
		  END
	END TRY
	BEGIN CATCH
		--- Log the error - if possible
		DECLARE @sResults nvarchar(MAX)
		SELECT @sResults = '[' + @progname + '] The database [' + @database_name + '] failed to backup (' + ISNULL(@sCommand, 'Unknown') + ').'

		IF ERROR_NUMBER() IS NOT NULL
			SELECT @sResults = @sResults + CHAR(13)
							+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
							+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
							+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
							+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

		--- Print the error message to sysout
		PRINT @sResults

		--- Insert the error in the database
		IF @sResults IS NOT NULL
		  BEGIN
			INSERT INTO [dbo].[tblMaint_Log] (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
				VALUES (@database_name, GETDATE(), N'backup_diff', 0, @sResults)

			--- Must raise an error so sqlcmd catches the severity level
			RAISERROR (@sResults, 16, 1)
			RETURN
		  END
		ELSE
		  BEGIN
			--- Must raise an error so sqlcmd catches the severity level
			SELECT @sResults = '[' + @progname + '] The database [' + ISNULL(@database_name, '') + '] failed to backup (' + ISNULL(@sCommand, '') + ').'
			RAISERROR (@sResults, 16, 1) WITH LOG
			RETURN
		  END
	END CATCH
  END
  
SET NOCOUNT OFF
